﻿

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using MySql.Data.MySqlClient;

public class DAL_tbl_siconide_furnace
{
    public DAL_tbl_siconide_furnace()
    {

    }



    public List<tbl_siconide_furnace> select(int ID)
    {
        List<tbl_siconide_furnace> list = new List<tbl_siconide_furnace>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select ID,Start_Date,Operator_Loading,Finish_Date,Operator_Unloading,Charge_Number,N2_K_m3_start,Ar_Pressure_start,H2_Pressure_start,He_Pressure_start,N2_K_m3_end,Ar_Pressure_end,H2_Pressure_end,He_Pressure_end,Remark,IS_ACTIVE,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE from tbl_siconide_furnace where ID=@ID";
        cmd.Parameters.AddWithValue("@ID", ID);
        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_siconide_furnace()
            {
                ID = Convert.ToInt32(dr["ID"]),
                Start_Date = dr["Start_Date"].toDateTimeWithoutError(),
                Operator_Loading = dr["Operator_Loading"].ToString(),
                Finish_Date = dr["Finish_Date"].toDateTimeWithoutError(),
                Operator_Unloading = dr["Operator_Unloading"].ToString(),
                Charge_Number = Convert.ToDecimal(dr["Charge_Number"]),
                N2_K_m3_start = Convert.ToDecimal(dr["N2_K_m3_start"]),
                Ar_Pressure_start = Convert.ToDecimal(dr["Ar_Pressure_start"]),
                H2_Pressure_start = Convert.ToDecimal(dr["H2_Pressure_start"]),
                He_Pressure_start = Convert.ToDecimal(dr["He_Pressure_start"]),
                N2_K_m3_end = Convert.ToDecimal(dr["N2_K_m3_end"]),
                Ar_Pressure_end = Convert.ToDecimal(dr["Ar_Pressure_end"]),
                H2_Pressure_end = Convert.ToDecimal(dr["H2_Pressure_end"]),
                He_Pressure_end = Convert.ToDecimal(dr["He_Pressure_end"]),
                Remark = dr["Remark"].ToString(),
                IS_ACTIVE = Convert.ToBoolean(dr["IS_ACTIVE"]),
                CREATED_BY = dr["CREATED_BY"].ToString(),
                CREATED_DATE = dr["CREATED_DATE"].toDateTimeWithoutError(),
                UPDATED_BY = dr["UPDATED_BY"].ToString(),
                UPDATED_DATE = dr["UPDATED_DATE"].toDateTimeWithoutError()
            });
        }

        return list;
    }



    public List<tbl_siconide_furnace> selectAll()
    {
        List<tbl_siconide_furnace> list = new List<tbl_siconide_furnace>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select ID,Start_Date,Operator_Loading,Finish_Date,Operator_Unloading,Charge_Number,N2_K_m3_start,Ar_Pressure_start,H2_Pressure_start,He_Pressure_start,N2_K_m3_end,Ar_Pressure_end,H2_Pressure_end,He_Pressure_end,Remark,IS_ACTIVE,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE from tbl_siconide_furnace Order By ID";

        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_siconide_furnace()
            {
                ID = Convert.ToInt32(dr["ID"]),
                Start_Date = dr["Start_Date"].toDateTimeWithoutError(),
                Operator_Loading = dr["Operator_Loading"].ToString(),
                Finish_Date = dr["Finish_Date"].toDateTimeWithoutError(),
                Operator_Unloading = dr["Operator_Unloading"].ToString(),
                Charge_Number = Convert.ToDecimal(dr["Charge_Number"]),
                N2_K_m3_start = Convert.ToDecimal(dr["N2_K_m3_start"]),
                Ar_Pressure_start = Convert.ToDecimal(dr["Ar_Pressure_start"]),
                H2_Pressure_start = Convert.ToDecimal(dr["H2_Pressure_start"]),
                He_Pressure_start = Convert.ToDecimal(dr["He_Pressure_start"]),
                N2_K_m3_end = Convert.ToDecimal(dr["N2_K_m3_end"]),
                Ar_Pressure_end = Convert.ToDecimal(dr["Ar_Pressure_end"]),
                H2_Pressure_end = Convert.ToDecimal(dr["H2_Pressure_end"]),
                He_Pressure_end = Convert.ToDecimal(dr["He_Pressure_end"]),
                Remark = dr["Remark"].ToString(),
                IS_ACTIVE = Convert.ToBoolean(dr["IS_ACTIVE"]),
                CREATED_BY = dr["CREATED_BY"].ToString(),
                CREATED_DATE = dr["CREATED_DATE"].toDateTimeWithoutError(),
                UPDATED_BY = dr["UPDATED_BY"].ToString(),
                UPDATED_DATE = dr["UPDATED_DATE"].toDateTimeWithoutError()
            });
        }

        return list;
    }

    public DataTable selectAllForGrid()
    {
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = @"Select distinct ID,sf.Start_Date as Start_Date,(select em.FIRST_NAME from tbl_employee_master em where em.USER_CODE=sf.Operator_Loading) as Operator_Loading,sf.Finish_Date as Finish_Date,(select em.FIRST_NAME from tbl_employee_master em where em.USER_CODE=sf.Operator_Unloading) as Operator_Unloading,sf.Charge_Number,sf.N2_K_m3_start,sf.Ar_Pressure_start,sf.H2_Pressure_start,sf.He_Pressure_start,sf.N2_K_m3_end,sf.Ar_Pressure_end,sf.H2_Pressure_end,sf.He_Pressure_end,sf.Remark,CASE WHEN sf.IS_ACTIVE =1 THEN 'True' ELSE 'False' END as 'IS_ACTIVE',sf.CREATED_BY,sf.CREATED_DATE as CREATED_DATE,sf.UPDATED_BY,sf.UPDATED_DATE 
                    from tbl_siconide_furnace sf LEFT JOIN tbl_employee_master em on sf.Operator_Loading=em.USER_CODE OR sf.Operator_Unloading=em.USER_CODE Order By sf.ID desc";

        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        return dt;
    }

    public int insert(DateTime Start_Date, String Operator_Loading, DateTime Finish_Date, String Operator_Unloading, Decimal Charge_Number, Decimal N2_K_m3_start, Decimal Ar_Pressure_start, Decimal H2_Pressure_start, Decimal He_Pressure_start, Decimal N2_K_m3_end, Decimal Ar_Pressure_end, Decimal H2_Pressure_end, Decimal He_Pressure_end, String Remark, String CREATED_BY, DateTime CREATED_DATE)
    {
        int i = 0;
        string sql = @"Insert into tbl_siconide_furnace(Start_Date, Operator_Loading, Finish_Date, Operator_Unloading, Charge_Number, N2_K_m3_start, Ar_Pressure_start, H2_Pressure_start, He_Pressure_start, N2_K_m3_end, Ar_Pressure_end, H2_Pressure_end, He_Pressure_end, Remark, CREATED_BY, CREATED_DATE)
                        values(@Start_Date, @Operator_Loading, @Finish_Date, @Operator_Unloading, @Charge_Number, @N2_K_m3_start, @Ar_Pressure_start, @H2_Pressure_start, @He_Pressure_start, @N2_K_m3_end, @Ar_Pressure_end, @H2_Pressure_end, @He_Pressure_end, @Remark, @CREATED_BY, @CREATED_DATE)";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@Start_Date", Start_Date);
        cmd.Parameters.AddWithValue("@Operator_Loading", Operator_Loading);
        cmd.Parameters.AddWithValue("@Finish_Date", Finish_Date);
        cmd.Parameters.AddWithValue("@Operator_Unloading", Operator_Unloading);
        cmd.Parameters.AddWithValue("@Charge_Number", Charge_Number);
        cmd.Parameters.AddWithValue("@N2_K_m3_start", N2_K_m3_start);
        cmd.Parameters.AddWithValue("@Ar_Pressure_start", Ar_Pressure_start);
        cmd.Parameters.AddWithValue("@H2_Pressure_start", H2_Pressure_start);
        cmd.Parameters.AddWithValue("@He_Pressure_start", He_Pressure_start);
        cmd.Parameters.AddWithValue("@N2_K_m3_end", N2_K_m3_end);
        cmd.Parameters.AddWithValue("@Ar_Pressure_end", Ar_Pressure_end);
        cmd.Parameters.AddWithValue("@H2_Pressure_end", H2_Pressure_end);
        cmd.Parameters.AddWithValue("@He_Pressure_end", He_Pressure_end);
        cmd.Parameters.AddWithValue("@Remark", Remark);
        cmd.Parameters.AddWithValue("@CREATED_BY", CREATED_BY);
        cmd.Parameters.AddWithValue("@CREATED_DATE", CREATED_DATE);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }



    public int update(int ID, DateTime Start_Date, String Operator_Loading, DateTime Finish_Date, String Operator_Unloading, Decimal Charge_Number, Decimal N2_K_m3_start, Decimal Ar_Pressure_start, Decimal H2_Pressure_start, Decimal He_Pressure_start, Decimal N2_K_m3_end, Decimal Ar_Pressure_end, Decimal H2_Pressure_end, Decimal He_Pressure_end, String Remark, String UPDATED_BY, DateTime UPDATED_DATE)
    {
        int i = 0;
        string sql = @"Update tbl_siconide_furnace SET Start_Date=@Start_Date, Operator_Loading=@Operator_Loading, Finish_Date=@Finish_Date, Operator_Unloading=@Operator_Unloading, Charge_Number=@Charge_Number, N2_K_m3_start=@N2_K_m3_start, Ar_Pressure_start=@Ar_Pressure_start, H2_Pressure_start=@H2_Pressure_start, He_Pressure_start=@He_Pressure_start, N2_K_m3_end=@N2_K_m3_end, Ar_Pressure_end=@Ar_Pressure_end, H2_Pressure_end=@H2_Pressure_end, He_Pressure_end=@He_Pressure_end, Remark=@Remark, UPDATED_BY=@UPDATED_BY, UPDATED_DATE=@UPDATED_DATE WHERE ID=@ID";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@ID", ID);
        cmd.Parameters.AddWithValue("@Start_Date", Start_Date);
        cmd.Parameters.AddWithValue("@Operator_Loading", Operator_Loading);
        cmd.Parameters.AddWithValue("@Finish_Date", Finish_Date);
        cmd.Parameters.AddWithValue("@Operator_Unloading", Operator_Unloading);
        cmd.Parameters.AddWithValue("@Charge_Number", Charge_Number);
        cmd.Parameters.AddWithValue("@N2_K_m3_start", N2_K_m3_start);
        cmd.Parameters.AddWithValue("@Ar_Pressure_start", Ar_Pressure_start);
        cmd.Parameters.AddWithValue("@H2_Pressure_start", H2_Pressure_start);
        cmd.Parameters.AddWithValue("@He_Pressure_start", He_Pressure_start);
        cmd.Parameters.AddWithValue("@N2_K_m3_end", N2_K_m3_end);
        cmd.Parameters.AddWithValue("@Ar_Pressure_end", Ar_Pressure_end);
        cmd.Parameters.AddWithValue("@H2_Pressure_end", H2_Pressure_end);
        cmd.Parameters.AddWithValue("@He_Pressure_end", He_Pressure_end);
        cmd.Parameters.AddWithValue("@Remark", Remark);
        cmd.Parameters.AddWithValue("@UPDATED_BY", UPDATED_BY);
        cmd.Parameters.AddWithValue("@UPDATED_DATE", UPDATED_DATE);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }



    public int delete(int ID)
    {
        int i = 0;
        string sql = "Delete FROM tbl_siconide_furnace WHERE ID=@ID";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@ID", ID);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }



}